package cn.ainit.fx;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import cn.ainit.sys.print.entity.SysPrint;
import cn.ainit.sys.query.entity.DataGridReceiveParams;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import net.sf.json.JSONObject;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import cn.ainit.sys.framework.FrameworkService;
import cn.ainit.sys.menu.SessionCont;
import cn.ainit.sys.query.entity.QueryJson;

@Controller
@RequestMapping("/fxa")
public class FaxingController {

    FaxingService faxingService;

    public FaxingService getFaxingService() {
        return faxingService;
    }

    @Resource
    public void setFaxingService(FaxingService faxingService) {
        this.faxingService = faxingService;
    }


    FrameworkService frameworkService;
    @Resource
    public void setFrameworkService(FrameworkService frameworkService) {
        this.frameworkService = frameworkService;
    }
    /***
     * 发行征订单获取折扣
     * @param keyVal
     * @param request
     * @param session
     * @return
     */
    @RequestMapping("/getZDdis")
    @ResponseBody
    public Map<String, Object> getDiscount(@RequestBody List<String> keyVal, HttpServletRequest request,HttpSession session){
        Map<String, Object> defValueMap = new HashMap<String, Object>();
        try {
            defValueMap = this.faxingService.getDiscount(keyVal,request,session);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return defValueMap;
    }
    /**
     * 查询选择单据中的数据（排除当前明细表中的数据）
     * @param dsId
     * @param clientId
     * @param ywIds
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/queryJS")
    @ResponseBody
    public QueryJson queryJS(String dsId,String clientId,String ywIds,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            if(ywIds !=null && !"".equals(ywIds)){
                sql = "SELECT * FROM ("+sql+") T WHERE T.CLIENTS_ID = '"+clientId+"' AND T.PX_ID NOT IN ("+ywIds+")";
            }else{
                sql = "SELECT * FROM ("+sql+") T WHERE T.CLIENTS_ID = '"+clientId+"'";
            }
            //执行sql 返回list<Object>
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }
    /**
     * 查询客户的预收单数据
     * @param dsId
     * @param clientId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/queryKUYS")
    @ResponseBody
    public QueryJson queryKUYS(String dsId,String clientId,String skId,String skidcache,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            if ("".equals(skidcache) || skidcache == null) {
                sql = sql+" AND NVL((SELECT SUM(nvl(sy.ys_money_use,0)+nvl(sy.ys_money_occupy,0)) FROM fx_sk_ys sy WHERE sy.ys_id=s.ys_id AND sy.sk_id NOT IN ('"+skId+"')),0)<s.ys_money AND  s.CLIENT_ID = '"+clientId+"'";
            }else if(!skidcache.equals(skId)){
                sql = sql+" AND NVL((SELECT SUM(nvl(sy.ys_money_use,0)+nvl(sy.ys_money_occupy,0)) FROM fx_sk_ys sy WHERE sy.ys_id=s.ys_id AND sy.sk_id NOT IN ('"+skId+"')),0)<s.ys_money AND  s.CLIENT_ID = '"+clientId+"'";
            }else{
                sql = sql+" AND NVL((SELECT SUM(nvl(sy.ys_money_use,0)+nvl(sy.ys_money_occupy,0)) FROM fx_sk_ys sy WHERE sy.ys_id=s.ys_id AND sy.sk_id NOT IN ('"+skId+"')),0)<s.ys_money AND  s.CLIENT_ID = '"+clientId+"' and SK_ID = '"+skId+"'";
            }
            //执行sql 返回list<Object>
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }
    /**
     * 结算清单中根据客户Id查询上期结余和预收结余
     * @param clientId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/jzDataByClient")
    @ResponseBody
    public Map<String,Object> jzDataByClient(String clientId,HttpServletRequest request) throws Exception{
        Double jsStr = 0.0;
        Double ysjy = 0.0;
        Double skcy = 0.0;
        Double zqk = 0.0;
        Map<String, Object> moneyJY = new HashMap<String, Object>();
        try {
            jsStr = this.faxingService.execFuncDZ(clientId);
            ysjy = this.faxingService.execYSFunc(clientId);
            skcy = this.faxingService.execSKCYFunc(clientId);
            zqk = this.faxingService.execZQKFunc(clientId);
            moneyJY.put("sqjy", jsStr);
            moneyJY.put("ysjy", ysjy);
            moneyJY.put("skcy", skcy);
            moneyJY.put("zqk", zqk);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return moneyJY;
    }
    /*
     * 销售对账根据客户ID获取所有能用的银行到款金额
     */
    @RequestMapping("/DKMByClient")
    @ResponseBody
    public List DKMByClient(String clientId) throws Exception{
        List list = new ArrayList();
        try {
            if(!"".equals(clientId)){
                list = this.faxingService.DKMByClient(clientId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    /***
     * 发行单据 获取默认值  库存
     * @param keyVal
     * @param request
     * @param session
     * @return
     */
    @RequestMapping("/getStockNumByHidStock")
    @ResponseBody
    public Map<String, Object> getStockNumByHidStock(@RequestBody List<String> keyVal, HttpServletRequest request,HttpSession session){
        Map<String, Object> defValueMap = new HashMap<String, Object>();
        try {
            defValueMap = this.faxingService.getStockNumByHidStock(keyVal,request,session);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return defValueMap;
    }



    /**
     * 根据库房得到商品数量不为零的架号
     * @param request
     * @return
     */
    @RequestMapping("/getShelfId")
    @ResponseBody
    public String getShelfIdByStockId(String stockId,String hId,HttpServletRequest request){
        String shelfId = "";
        try {
            shelfId = this.faxingService.findShelfId(stockId, hId);
            if ("".equals(shelfId) || shelfId == null) {
                shelfId = "";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return shelfId;
    }
    /**
     * 根据商品ID查询库存商品的数量
     * @param request
     * @return
     */
    @RequestMapping("/getAmountByHid")
    @ResponseBody
    public String getAmountByHid(String stockId,String hId,HttpServletRequest request){
        String hAmount = "";
        try {
            hAmount = this.faxingService.getAmountByHid(hId);
            if ("".equals(hAmount) || hAmount == null) {
                hAmount = "";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hAmount;
    }

    /**
     * 采样数据
     * @param samples
     * @param request
     * @param session
     * @return
     */
    @RequestMapping("/doSampling")
    @ResponseBody
    public SamplingList doSampling(@RequestBody SamplingList samples, HttpServletRequest request,HttpSession session){
        SamplingList reObj = new SamplingList();
        samples.setOid((String) session.getAttribute(SessionCont.uid));
        /**
         * flag = 0 添加采样      如果没有本条数据则直接添加
         * 				            如果已经存在该商品，则直接返回，根据用户操作决定是否添加
         * flag = 00 叠加采样  已经确认返回的数据，叠加
         *
         * flag = 1 覆盖采样	  直接将采样数据写入到数据库
         * flag = 2 提取采样     直接读取采样数据
         */
        try {
            if ("0".equals(samples.getFlag())) {
                reObj = this.faxingService.addSamples(samples); //返回现有的数据
            }else if ("00".equals(samples.getFlag())) {
                if (this.faxingService.addSamplesCount(samples)) {
                    reObj.setMsg("success");
                }else {
                    reObj.setMsg("fail");
                }
            }else if ("1".equals(samples.getFlag())) {
                if (this.faxingService.saveSamples(samples)) {
                    reObj.setMsg("success");
                }else {
                    reObj.setMsg("fail");
                }
            }else if ("2".equals(samples.getFlag())) {
                reObj = this.faxingService.getSamples(samples.getOid());
            }else{
                samples.setMsg("fail");
                samples.setInfo("参数有误，请联系开发");
            }
        } catch (Exception e) {
            e.printStackTrace();
            reObj.setMsg("fail");
            reObj.setInfo(e.getMessage());
        }
        return reObj;
    }
    /**
     * 保存客户收款单和预收表的关联表
     * @param jsId
     * @param ysAllOption
     * @param request
     * @param session
     * @return
     * modify：原为对账结算单和预收单的关联，闲修改为客户收款单和预收表的关联
     * @throws Exception
     */
    @RequestMapping("/saveSYYS")
    @ResponseBody
    public boolean saveDZYS(String skId,String cid,@RequestBody List<List<Map<String, String>>> skOptions,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flag = this.faxingService.saveDZYSFunc(skId,cid,skOptions.get(0), uid.toString()); //保存收款和预收的关系
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    @RequestMapping("/saveSYDK")
    @ResponseBody
    public boolean saveSYDK(String skId,String cid,@RequestBody List<List<Map<String, String>>> skOptions,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flag = this.faxingService.saveSKDKFunc(skId,cid,skOptions.get(0), uid.toString()); //保存收款和到款的关系
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 保存客户销售单和销退单的关联表
     * @param jsId
     * @param ysAllOption
     * @param request
     * @param session
     * @return
     * modify：原为对账结算单和预收单的关联，闲修改为客户收款单和预收表的关联
     * @throws Exception
     */
    @RequestMapping("/saveXSXT")
    @ResponseBody
    public boolean saveXSXT(String xtId,@RequestBody List<Map<String, String>> xtOptions,String clientId,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flag = this.faxingService.saveXSXTFunc(xtId, xtOptions, uid.toString(),clientId); //保存收款和预收的关系
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 保存冲减弹窗的临时数据
     * @param selRows
     * @param request
     * @param session
     * @return
     * @throws Exception
     */
    @RequestMapping("/saveTempPro")
    @ResponseBody
    public boolean saveTempPro(@RequestBody List<Map<String, String>> selRows,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            flag = this.faxingService.saveTempDataFunc(selRows);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }


    /**
     * 保存冲减弹窗的临时数据
     * @param selRows
     * @param request
     * @param session
     * @return
     * @throws Exception
     */
    @RequestMapping("/saveCacheYS")
    @ResponseBody
    public boolean saveCacheYS(@RequestBody FxReturnObj maps,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            flag = this.faxingService.saveCacheYS(maps.getSelRows(),maps.getSkid());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }


    /**
     * 保存冲减弹窗的临时数据
     * @param selRows
     * @param request
     * @param session
     * @return
     * @throws Exception
     */
    @RequestMapping("/saveCacheDK")
    @ResponseBody
    public boolean saveCacheDK(@RequestBody FxReturnObj maps,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            flag = this.faxingService.saveCacheDK(maps.getSelRows(),maps.getSkid());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 删除临时表数据（一行或多行）
     * @param selRows
     * @param request
     * @param session
     * @return
     * @throws Exception
     */
    @RequestMapping("/delTempPro")
    @ResponseBody
    public boolean delTempPro(@RequestBody List<Map<String, String>> getDelRows,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            flag = this.faxingService.delTemoDataFunc(getDelRows);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 删除临时表数据（所有行）
     * @param selRows
     * @param request
     * @param session
     * @return
     * @throws Exception
     */
    @RequestMapping("/delAllTempPro")
    @ResponseBody
    public boolean delAllTempPro(HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            String sql = " DELETE FROM FX_XTCJ";
            this.faxingService.execSqlB(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 查询客户收款中的使用金额的数据
     * @param jsId 现应为客户收款单中的 主键（这里没有修改变量名称）
     * @param request
     * @return
     * modify：原为对账结算单中的使用预收金额，则后面将使用预收部分修改到客户收款单中。
     * @throws Exception
     */
    @RequestMapping("/findDzysData")
    @ResponseBody
    public List findDZYSData(String skId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            if(!"".equals(skId)){
                list = this.faxingService.getDzysByJsId(skId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
     * 查询客户收款中使用到款的数据
     * @param skId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/findDkskData")
    @ResponseBody
    public List findDkskData(String skId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            if(!"".equals(skId)){
                list = this.faxingService.getDkskBySkId(skId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
     * 销售退货中的冲减记录
     * @param xtId 销退Id
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/findCJxtData")
    @ResponseBody
    public List findCJxtData(String xtId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            if(!"".equals(xtId)){
                list = this.faxingService.getCJxtByXtId(xtId);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
     * 销售退货中的临时表数据
     * @return
     * @throws Exception
     */
    @RequestMapping("/findTempData")
    @ResponseBody
    public List findTempData(HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            list= this.faxingService.getTempData();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     *收款核销中的临时表数据（预收）
     * @return
     * @throws Exception
     */
    @RequestMapping("/findYsData")
    @ResponseBody
    public List findYsData(String skId,String clientId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            list= this.faxingService.findYsData(skId,clientId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     *收款核销中的临时表数据（到款）
     * @return
     * @throws Exception
     */
    @RequestMapping("/findDkData")
    @ResponseBody
    public List findDkData(String skId,String clientId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            list= this.faxingService.findDkData(skId,clientId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 新书分发中  根据客户Id判断客户多地址的数量
     * @param cid
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/addressByCId")
    @ResponseBody
    public Integer addressByCId(String cid,HttpServletRequest request) throws Exception{
        int result =0;
        try {
            result = this.faxingService.findCountByCId(cid);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    /**
     * 根据客户Id得到客户的地址
     * @param clientId
     * @return
     * @throws Exception
     * @author wyy
     */
    @RequestMapping("/addrByClient")
    @ResponseBody
    public String addrByClient(String clientId,HttpServletRequest request) throws Exception{
        String addressId = "";
        try {
            addressId = this.faxingService.findAdress(clientId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return addressId;
    }
    /**
     * 根据印刷厂Id（印刷说明单明细Id去查询）找到对应的仓库
     * @return
     * @throws Exception
     */
    @RequestMapping("/findStockByYSCId")
    @ResponseBody
    public String findStockByYSCId(String peItemId,HttpServletRequest request) throws Exception{
        String stockId = "";
        try {
            stockId = this.faxingService.findStockId(peItemId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return stockId;
    }
    /**
     * 根据对账结算Id查询该表的明细数据
     * @param jsId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/getDZItemList")
    @ResponseBody
    public List getDZItemList(String djId,String flag,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            list = this.faxingService.getDzItemByJSId(djId,flag);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    @RequestMapping("/dzjsByClient")
    @ResponseBody
    public int dzjsByClient(String clientId,HttpServletRequest request) throws Exception{
        int num =0;
        try {
            String sql = " select count(1) FROM fx_clients_jslist j WHERE NVL(j.is_verify,'0') = '0' AND NVL(j.is_destroy,'0') = '0' AND j.client_id = '"+clientId+"'";
            num = this.faxingService.execSqlToInt(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    /**
     * 查询弹窗范围的数据
     * @param clientId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/getTcData")
    @ResponseBody
    public Map<String, Object> getTcData(String clientId,HttpServletRequest request) throws Exception {
        Map<String, Object> defValueMap = new HashMap<String, Object>();
        String clientZq = "";
        List dataList = new ArrayList();
        try {
	/*		clientZq = this.faxingService.zqByClientId(clientId);
			if ("".equals(clientZq) || clientZq == null || "null".equals(clientZq)) {
				defValueMap.put("flag", "0");
			}else {*/
            defValueMap.put("flag","1");
            dataList = this.faxingService.listByClientId(clientId);
            //}
            defValueMap.put("dataList",dataList);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return defValueMap;
    }



    @RequestMapping("/getXsDzTcData")
    @ResponseBody
    public QueryJson getXsDzTcData(String clientId,HttpServletRequest request) throws Exception {
        Map<String, Object> defValueMap = new HashMap<String, Object>();
        QueryJson queryJson=new QueryJson();
        try {
            defValueMap.put("flag","1");
            queryJson.setRows(this.faxingService.listByClientId(clientId));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }




    /**
     * 新书分发根据业务员自动拉取对应的值
     * @param oIdLastgr		业务员
     * @throws 王康
     */
    @RequestMapping("/getkhData")
    @ResponseBody
    public Map<String, Object> getkhData(String oIdLastgr,String hid,HttpServletRequest request) throws Exception {
        Map<String, Object> defValueMap = new HashMap<String, Object>();
        List dataList = new ArrayList();
        try {
            dataList = this.faxingService.listByoid(oIdLastgr,hid);
            defValueMap.put("dataList",dataList);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return defValueMap;
    }


    /**
     * 根据开票Id查询该开票单是否已经生成收款单
     * @param invId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/countByKPId")
    @ResponseBody
    public int countByKpId(String invId,HttpServletRequest request) throws Exception{
        int count = 0;
        try {
            count = this.faxingService.pdCountByKPId(invId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }
    /**
     * 查询客户的到款单数据
     * @param dsId
     * @param clientId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/queryKUDK")
    @ResponseBody
    public QueryJson queryKUDK(String dsId,String clientId,String skid,String skidcache,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            // if ("".equals(skidcache) || skidcache == null) {
            sql = sql+" AND NVL((SELECT SUM(NVL(dk.dk_money_use, 0) + NVL(dk.dk_money_occupy, 0)+NVL((SELECT SUM(Y.YS_MONEY) DK_TOTAL_MONEY FROM FX_YS Y WHERE Y.SOURCE_ID =D.DK_ID),0)) DK_TOTAL_MONEY FROM FX_SK_DK dk WHERE   DK.dk_id =D.DK_ID AND dk.sk_id NOT IN ('"+skid+"')) ,0) < abs(d.dk_money) AND d.CLIENT_ID = '"+clientId+"'";
            //}else if(!skidcache.equals(skid)){
            //	sql = sql+"  AND  NVL((SELECT SUM(NVL(dk.dk_money_use,0)+NVL(dk.dk_money_occupy,0)) FROM FX_SK_DK dk WHERE dk.dk_id=d.dk_id AND dk.sk_id NOT IN ('"+skid+"') ),0)<d.dk_money AND d.CLIENT_ID = '"+clientId+"'";
            // }else{
            // 	sql = sql+"  AND  NVL((SELECT SUM(NVL(dk.dk_money_use,0)+NVL(dk.dk_money_occupy,0)) FROM FX_SK_DK dk WHERE dk.dk_id=d.dk_id AND dk.sk_id NOT IN ('"+skid+"') ),0)<d.dk_money AND d.CLIENT_ID = '"+clientId+"' and sk_id = '"+skid+"'";
            //}
            //执行sql 返回list<Object>
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /**
     * 查询付款对象的预付单数据
     * @param dsId
     * @param MAN_BUSINESS
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/findYFD")
    @ResponseBody
    public QueryJson findYFD(String dsId,String MAN_BUSINESS,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            sql="select * from ("+sql+")temp where temp.man_business='"+MAN_BUSINESS+"' and temp.is_fk='1'";
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }
    /**
     * 设置预付单的默认值
     * @param dsId
     * @param MAN_BUSINESS
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/UpdateDefault")
    @ResponseBody
    public boolean UpdateDefault(String dsId,String MAN_BUSINESS,HttpServletRequest request) throws Exception{
        boolean flag = false;
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "update sys_columns set default_value_qr='C-"+MAN_BUSINESS+"' where ds_id='"+dsId+"' and name='MAN_BUSINESS'";
            this.faxingService.execSqlB(storeSql);
            flag=true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 设置预付单的默认值
     * @param dsId
     * @param MAN_BUSINESS
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/UpdateDefaultOtherck")
    @ResponseBody
    public boolean UpdateDefaultOtherck(String dsId,String STOCK_ID,String DJ_TYPE,HttpServletRequest request) throws Exception{
        boolean flag = false;
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "update sys_columns set default_value_qr='C-"+STOCK_ID+"' where ds_id='"+dsId+"' and name='CLSTOCK'";
            this.faxingService.execSqlB(storeSql);
            flag=true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 在选择库房之后，修改明细中去向的值
     * @param dzId
     * @param valueStr
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/getChinese")
    @ResponseBody
    public List getChinese(String dzId,String manId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        boolean flag = false;
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "SELECT * FROM sys_dz_source S WHERE S.Dz_ID = '"+dzId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            sql="select * from ("+sql+")temp where temp.man_id='"+manId+"'";
            list = this.faxingService.execSql(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 根据合同号获取获取所有选题ID
     * @param dzId
     * @param valueStr
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/getXtIDs")
    @ResponseBody
    public List getXtIDs(String cl_hetong,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        boolean flag = false;
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "SELECT ck.XT_ID FROM VV_CL_OUTSTOCK ck where ck.CL_HETONGHAO='"+cl_hetong+"' and ck.XT_ID is not null group by ck.XT_ID";
            list = this.faxingService.execSql(storeSql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 根据合同号和选题ID查询印次
     * @param dzId
     * @param valueStr
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/getyinci")
    @ResponseBody
    public List getyinci(String cl_hetong,String xtid,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        boolean flag = false;
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "SELECT ck.YINCI FROM VV_CL_OUTSTOCK CK where ck.CL_HETONGHAO='"+cl_hetong+"' and ck.XT_ID='"+xtid+"'";
            list = this.faxingService.execSql(storeSql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询付款对象的还款单数据
     * @param dsId
     * @param MAN_BUSINESS
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/findHKD")
    @ResponseBody
    public QueryJson findHKD(String dsId,String MAN_BUSINESS,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            sql="select * from ("+sql+")temp where temp.man_business='"+MAN_BUSINESS+"' and temp.is_fk='1'";
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }
    /**
     * 把选择的付款单从关联表中删除,并把新的数据显示出来
     */
    @RequestMapping("/deleteFY")
    @ResponseBody
    public QueryJson deleteFY(String bm_id,String source_id,String fj_id,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "DELETE FROM DS_YFYDAOGL BG WHERE BG.BM_ID='"+bm_id+"' AND BG.SOURCE_ID='"+source_id+"' AND BG.FJ_ID='"+fj_id+"'";
            this.faxingService.execSqlB(storeSql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }
    /**
     * 判断该月客户是否上报过数据
     */
    @RequestMapping("/getCIdByDate")
    @ResponseBody
    public QueryJson getCIdByDate(String clientId,String Date,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据客户ID判断该客户是否在该月上报过数据
            String storeSql = "select * from cw_monthsx cm where cm.client_id='"+clientId+"' and cm.is_destroy='0' and  to_char(cm.import_date,'YYYY') = to_char(to_date('2016-02-01','yy-mm-dd hh24:mi:ss'),'YYYY') and to_char(cm.import_date,'MM') = to_char(to_date('"+Date+"','yy-mm-dd hh24:mi:ss'),'MM')";
            list = this.faxingService.execSql(storeSql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }
    /**
     * 判断客户有没有跳号
     */
    @RequestMapping("/getCIdByDateNext")
    @ResponseBody
    public QueryJson getCIdByDateNext(String clientId,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据客户ID判断该客户是否在该月上报过数据
            String storeSql = "select max(to_char(cx.import_date,'mm')) mouth,max(to_char(cx.import_date,'yy')) years from cw_monthsx cx where cx.client_id='"+clientId+"' and cx.is_destroy='0'";
            list = this.faxingService.execSql(storeSql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /**
     * 把选择的还款单从关联表中删除,并把新的数据显示出来
     */
    @RequestMapping("/deleteHK")
    @ResponseBody
    public QueryJson deleteHK(String HK_ID,String MAN_BUSINESS,String fj_id,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            String storeSql = "delete from ds_hkinfo di where di.hk_id='"+HK_ID+"' and di.man_business='"+MAN_BUSINESS+"' and di.fj_id='"+fj_id+"'";
            this.faxingService.execSqlB(storeSql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }
    /**
     * 查询编务费用和报销费用
     * @param dsId
     * @param MAN_BUSINESS
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/findFKD")
    @ResponseBody
    public QueryJson findFKD(String dsId,String MAN_BUSINESS,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            sql="select * from ("+sql+")temp where temp.man_business='"+MAN_BUSINESS+"'";
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }


    /**
     * 查询付款对象的预付单数据
     * @param dsId
     * @param MAN_BUSINESS
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/findOtherCk")
    @ResponseBody
    public QueryJson findOtherCk(String dsId,String DJ_TYPE,String STOCK_ID,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            sql="select * from ("+sql+")temp where temp.CLSTOCK='"+STOCK_ID+"'";
            list = this.faxingService.execSql(sql);
            System.out.print(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /**
     * 材料对账弹窗界面数据查询
     * @param dsId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/findClCk")
    @ResponseBody
    public QueryJson findClCk(String dsId,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单和借款单
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            list = this.faxingService.execSql(sql);
            System.out.print(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }



    /**
     * 待发货区中的业务审核执行的方法
     * @param runIds
     * @return
     * @throws Exception
     */
    @RequestMapping("/ywVerifyFunc")
    @ResponseBody
    public List<String> ywVerifyFunc(@RequestBody List<String> runIds,HttpServletRequest request,HttpSession session) throws Exception{
        String flag = "";
        List<String> flagStr = new ArrayList<String>();
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flag = this.faxingService.wyVerifyYW(runIds,uid.toString());
            flagStr.add(flag);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flagStr;
    }
    /**
     * 待发货区中的部门审核执行的方法
     * @param runIds
     * @return
     * @throws Exception
     */
    @RequestMapping("/deapVerifyFunc")
    @ResponseBody
    public List<String> deapVerifyFunc(@RequestBody List<String> runIds,HttpServletRequest request,HttpSession session) throws Exception{
        String flagStr1 = "";
        List<String> flagStr = new ArrayList<String>();
//		return flagStr;
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flagStr1 = this.faxingService.wyVerifyDeap(runIds,uid.toString());
            flagStr.add(flagStr1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flagStr;
    }

    /**
     * 成本核算中公式计算
     * @param runIds
     * @return
     * @throws Exception
     */
    @RequestMapping("/gscalCulaButn")
    @ResponseBody
    public List<String> gscalCulaButn(@RequestBody List<String> runIds,HttpServletRequest request,HttpSession session) throws Exception{
        String flagStr1 = "";
        List<String> flagStr = new ArrayList<String>();
//		return flagStr;
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flagStr1 = this.faxingService.gscalCulaButn(runIds,uid.toString());
            flagStr.add(flagStr1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flagStr;
    }
    /**
     * 成本核算中数量*单价
     * @param runIds
     * @return
     * @throws Exception
     */
    @RequestMapping("/amountUnitPrice")
    @ResponseBody
    public List<String> amountUnitPrice(@RequestBody List<String> runIds,HttpServletRequest request,HttpSession session) throws Exception{
        String flagStr1 = "";
        List<String> flagStr = new ArrayList<String>();
//		return flagStr;
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flagStr1 = this.faxingService.amountUnitPrice(runIds,uid.toString());
            flagStr.add(flagStr1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flagStr;
    }
    /**
     * 待发货区中的保存方法调用
     * @param runIds
     * @return
     * @throws Exception
     */
    @RequestMapping("/dfhqCommSave")
    @ResponseBody
    public boolean dfhqCommSave(String flagStr,@RequestBody List<Map<String, String>> allRows,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
//			if("0".equals(flag)){  //业务审核
//				flag = this.faxingService.wySaveFunc(allRows);
//			}else if("1".equals(flagStr)){ //部门审核
            flag = this.faxingService.bmSaveFunc(allRows);
//			}
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 待发货区中的确认发货的按钮
     * @param runIds
     * @return
     * @throws Exception
     */
    @RequestMapping("/sureFHFunc")
    @ResponseBody
    public List<String> sureFHFunc(HttpServletRequest request,HttpSession session) throws Exception{
        String flagStr= "";
        String runids = request.getParameter("runids");
        List<String> fangstr1 = new ArrayList<String>();
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flagStr = this.faxingService.sureFH(uid.toString(),runids);
            fangstr1.add(flagStr);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return fangstr1;
    }
    /**
     * 待发货区中取消验证按钮
     * @param runIds
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/qxDeapVerify")
    @ResponseBody
    public boolean qxDeapVerify(@RequestBody List<String> runIds,HttpServletRequest request) throws Exception{
        boolean flag = false;
        try {
            flag = this.faxingService.qxDeapVerifyFunc(runIds);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 待发货区中的确认发货的按钮
     * @param runIds
     * @return
     * @throws Exception
     */
    @RequestMapping("/clientsureFH")
    @ResponseBody
    public List<String> clientsureFH(HttpServletRequest request,HttpSession session) throws Exception{
        String flagStr= "";
        String runids = request.getParameter("runids");
        List<String> fangstr1 = new ArrayList<String>();
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flagStr = this.faxingService.clientsureFH(uid.toString(),runids);
            fangstr1.add(flagStr);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return fangstr1;
    }




	/**
	 *纸厂收款核销中的数据（预收）
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("/findZcYsData")
	@ResponseBody
	public List findZcYsData(String VENDORID,HttpServletRequest request) throws Exception{
		List list = new ArrayList();
		try {
			list= this.faxingService.findZcYsData(VENDORID);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}







    /**
     *出版社中的临时表数据（预收）查询出版社预收的预付单数据
     * @return
     * @param ds_id
     * @param clientId
     * @throws Exception
     */
    @RequestMapping("/findCBSHXYs")
    @ResponseBody
    public QueryJson findCBSHXYs(String dsId,String clientId,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            sql="select * from ("+sql+") temp where temp.PUB_ID='"+clientId+"'";
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /**
     * 设置出版社预付单的默认值
     * @param dsId
     * @param VENDOR_ID
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/queryCBSYS")
    @ResponseBody
    public QueryJson queryCBSYS(String dsId,String clientId,String chid,String skidcache,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            String storeSql = "SELECT * FROM SYS_DATASTORE VV WHERE VV.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            if ("".equals(skidcache) || skidcache == null) {
                sql = sql+" AND NVL((SELECT SUM(nvl(sy.ys_money_use,0)+nvl(sy.ys_money_occupy,0)) FROM CW_CBS_YS sy WHERE sy.ys_id=VV.SGFYFD_ID AND sy.sk_id NOT IN ('"+chid+"')),0)<VV.PREPAID_AMOUNT AND  VV.PUB_ID = '"+clientId+"'";
            }else if(!skidcache.equals(chid)){
                sql = sql+" AND NVL((SELECT SUM(nvl(sy.ys_money_use,0)+nvl(sy.ys_money_occupy,0)) FROM CW_CBS_YS sy WHERE sy.ys_id=VV.SGFYFD_ID AND sy.sk_id NOT IN ('"+chid+"')),0)<VV.PREPAID_AMOUNT AND  VV.PUB_ID = '"+clientId+"'";
            }else{
                sql = sql+" AND NVL((SELECT SUM(nvl(sy.ys_money_use,0)+nvl(sy.ys_money_occupy,0)) FROM CW_CBS_YS sy WHERE sy.ys_id=VV.SGFYFD_ID AND sy.sk_id NOT IN ('"+chid+"')),0)<VV.PREPAID_AMOUNT AND  VV.PUB_ID = '"+clientId+"' and SK_ID = '"+chid+"'";
            }
            //执行sql 返回list<Object>
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }
    /***
     * 客户预收，清除金额的方法
     */
    @RequestMapping("/cbsremovemoney")
    @ResponseBody
    public String cbsremovemoney(String chid,HttpServletRequest request,HttpSession session) throws Exception{
        String str = "";
        try {
            str = this.faxingService.cbsremovemoney(chid);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return str;
    }


    /**
     * 保存出版社和预收表的关联表
     * @param jsId
     * @param ysAllOption
     * @param request
     * @param session
     * @return
     * modify：原为对账结算单和预收单的关联，闲修改为客户收款单和预收表的关联
     * @throws Exception
     */
    @RequestMapping("/saveCBSSYYS")
    @ResponseBody
    public boolean saveCBSSYYS(String skId,String YSID,String PUB_ID,@RequestBody List<List<Map<String, String>>> skOptions,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flag = this.faxingService.saveCBSDZYSFunc(skId,YSID, PUB_ID, skOptions.get(0), uid.toString()); //保存收款和预收的关系
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }



    /**
     * 业务取消发货
     * @param runIds
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/ywQxFH")
    @ResponseBody
    public boolean ywQxFH(String runIds,HttpServletRequest request) throws Exception{
        boolean flag = false;
        try {
            flag = this.faxingService.qxYwFH(runIds);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 开票单中的确认开票按钮执行的方法
     * @param runIds
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/kpSureFunc")
    @ResponseBody
    public boolean kpSureFunc(String kpId,HttpServletRequest request) throws Exception{
        boolean flag = false;
        try {
            flag = this.faxingService.sureKP(kpId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 确认开票，更改开票状态
     * @param runIds
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/kpsqzt")
    @ResponseBody
    public boolean kpsqzt(String kpId,HttpServletRequest request) throws Exception{
        boolean flag = false;
        try {
            flag = this.faxingService.kpsqzt(kpId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 银行到款中 剩余金额转预收
     * @param runIds
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/bankDkToYs")
    @ResponseBody
    public String bankDkToYs(String djId,HttpServletRequest request,HttpSession session) throws Exception{
        String returnStr = "";
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            returnStr = this.faxingService.dkToYSFunc(djId, uid.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return returnStr;
    }
    /**
     * 销售出库中的根据客户ID拉取客户的地址，如果多地址不默认，单地址默认为那条数据
     * @param clientId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/moreAddressByCId")
    @ResponseBody
    public List moreAddressByCId(String clientId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            list = this.faxingService.moreAddByCId(clientId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }



    /************************************************************
     * 客户添货中的根据客户ID拉取客户的地址，如果多地址不默认，单地址默认为那条数据
     * @param clientId
     * @param request
     * @return
     * @throws 王康
     *************************************************************/
    @RequestMapping("/getAddressGoodsByCId")
    @ResponseBody
    public List getAddressGoodsByCId(String cId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            list = this.faxingService.getAddressGoodsByCId(cId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 银行到款回车事件，根据银行账号，返回客户名称
     * @param clientId
     * @param request
     * @return
     * @throws 王康
     **/
    @RequestMapping("/getDkAccountByClient")
    @ResponseBody
    public List getDkAccountByClient(String bankaccount,HttpServletRequest request,HttpSession session) throws Exception{
        List list = new ArrayList();
        try {
            list = this.faxingService.getDkAccountByClient(bankaccount);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /***
     * 客户收款，清除金额的方法
     */
    @RequestMapping("/removemoney")
    @ResponseBody
    public String removemoney(String chid,HttpServletRequest request,HttpSession session) throws Exception{
        String str = "";
        try {
            str = this.faxingService.removemoney(chid);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return str;
    }
    /***
     * 客户预收，清除金额的方法
     */
    @RequestMapping("/cjbremovemoney")
    @ResponseBody
    public String cjbremovemoney(String chid,HttpServletRequest request,HttpSession session) throws Exception{
        String str = "";
        try {
            str = this.faxingService.cjbremovemoney(chid);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return str;
    }

    /***
     *    数据组织
     */
    @RequestMapping("/data_tf")
    @ResponseBody
    public List<Object> data_tf(@RequestBody List<Map<String, String>> selRows,HttpServletRequest request,HttpSession session) throws Exception{
        Map<String,Object> map = new HashMap<String, Object>();
        if(selRows.size()>0)
        {
            String itme = "itme";
            Integer num = 0;
            for(int i=0;i<selRows.size();i++){
                String H_ID = selRows.get(i).get("H_ID");
                String H_DISCOUNT =	selRows.get(i).get("H_DISCOUNT");
                String H_DISCOUNT_MOD =selRows.get(i).get("H_DISCOUNT_MOD");
                String key = H_ID+H_DISCOUNT+H_DISCOUNT_MOD;
                if(map.get(key)==null){
                    map.put(key, selRows.get(i));
                }
                else{
                    Map<String, Object> map_ = (Map) map.get(key);
                    num = Integer.parseInt(map_.get("H_AMOUNT_SY")+"") + Integer.parseInt(selRows.get(i).get("H_AMOUNT_SY")+"");
                    map_.put("H_AMOUNT_SY", num);
                }

            }
        }
        List<Object> list = new ArrayList<Object>();
        for (Entry<String, Object> entry : map.entrySet()) {
            String key = entry.getKey().toString();
            list.add(map.get(key));
        }

        return list;
    }

    /**
     * 包册数
     */
    @RequestMapping("/CountPackNum")
    @ResponseBody
    public List CountPackNum(String hid,String amount,HttpServletRequest request,HttpSession session) throws Exception{
        List list = new ArrayList();
        try {
            list = this.faxingService.CountPackNum(hid,amount);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @RequestMapping("/getXtDiscount")
    @ResponseBody
    public String getXtDiscount(String cid,String hId,HttpServletRequest request){
        String XtDiscount = "";
        try {
            XtDiscount = this.faxingService.getXtDiscount(cid, hId);
            if ("".equals(XtDiscount) ||XtDiscount == null) {
                XtDiscount = "";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return XtDiscount;
    }



    @RequestMapping("/loadColnList")
    @ResponseBody
    public Map<String,List> loadColnList(@RequestBody List<Map<String, Object>> dsIdObjs,HttpServletRequest request,HttpSession session){
        Object uid = session.getAttribute(SessionCont.uid);
        if(uid == null || "".equals(uid.toString())){
            System.out.println("用户未登录..FrameworkController L.506");
        }
        Map<String,List> maps=new HashMap<String, List>();
        List columnsList = new ArrayList();
        try {
            //根据dsId 加载不同页面的列集合
            for (int i = 0; i < dsIdObjs.size(); i++) {
                String dsId= (String) dsIdObjs.get(i).get("dsid");
                String type= (String) dsIdObjs.get(i).get("type");
                columnsList = frameworkService.loadColumnsU(dsId, type,"",uid==null?"":uid.toString());
                maps.put(dsId, columnsList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return maps;
    }


    @RequestMapping("/yfdSaveBefore")
    @ResponseBody
    public String yfdSaveBefore(@RequestBody List<Map<String, Object>> allRows,HttpServletRequest request,HttpSession session){
        try {
            return  faxingService.yfdSaveBefore(allRows, request, session);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }
    /**
     *根据对象id，获取银行账号
     * @param xtId
     * @param request
     * @return
     */
    @RequestMapping("/getcontractnobyzcid")
    @ResponseBody
    public List getcontractnobyzcid(String VENDOR_ID,HttpServletRequest request,HttpSession session){
        return faxingService.getcontractnobyzcid(VENDOR_ID);
    }




    /**
     * 查询纸厂预收的预付单数据
     * @param dsId
     * @param VENDOR_ID
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/findzcHxYFD")
    @ResponseBody
    public QueryJson findzcHxYFD(String dsId,String VENDOR_ID,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            sql="select * from ("+sql+")temp where temp.VENDOR_ID='"+VENDOR_ID+"'";
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }


    /**
     * 设置预付单的默认值
     * @param dsId
     * @param VENDOR_ID
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/zcHxDefault")
    @ResponseBody
    public boolean zcHxDefault(String dsId,String VENDOR_ID,HttpServletRequest request) throws Exception{
        boolean flag = false;
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单
            String storeSql = "update sys_columns set default_value_qr='C-"+VENDOR_ID+"' where ds_id='"+dsId+"' and name='VENDOR_ID'";
            this.faxingService.execSqlB(storeSql);
            flag=true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }


    /**
     * 点击删除后，修改预付单的数据
     */
    @RequestMapping("/updateFY")
    @ResponseBody
    public QueryJson updateFY(String zc_id,String VENDOR_ID,String yf_id ,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单
            String storeSql = "DELETE FROM zc_yfdaok BG WHERE BG.BM_ID='"+zc_id+"' AND BG.VENDOR_ID='"+VENDOR_ID+"'AND yf_id='"+yf_id+"'";
            this.faxingService.execSqlB(storeSql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }






    /**
     *根据出版社id，获取银行账号
     * @param xtId
     * @param request
     * @return
     */
    @RequestMapping("/getcontractnobycbsid")
    @ResponseBody
    public List getcontractnobycbsid(String VENDOR_ID,HttpServletRequest request,HttpSession session){
        return faxingService.getcontractnobycbsid(VENDOR_ID);
    }

    /**
     *根据付款对象id，拉对对应的 所有已对账未收票的对账单   (作者发票单使用)
     * @param VENDOR_ID
     * @param request
     * @return
     */
    @RequestMapping("/getcontractnobyfkdxid")
    @ResponseBody
    public List getcontractnobyfkdxid(String VENDOR_ID,HttpServletRequest request,HttpSession session){
        return faxingService.getcontractnobyfkdxid(VENDOR_ID);
    }



    /**
     *根据付款对象id，拉对对应的 所有已对账未收票,未被使用的 作者对账单    (作者核销单使用)
     * @param VENDOR_ID
     * @param request
     * @return
     */
    @RequestMapping("/getcontractnobyzzfkdxid")
    @ResponseBody
    public List getcontractnobyzzfkdxid(String hxdId, Long begin, Long end, String XT_ID, String VENDOR_ID,String AUTHOR, HttpServletRequest request,HttpSession session){
        return faxingService.getcontractnobyzzfkdxid(hxdId, begin==null?null:new Date(begin), end==null?null:new Date(end), XT_ID, VENDOR_ID, AUTHOR);
    }


    /**
     * 保存冲减弹窗的临时数据   (作者核销单使用)
     * @param selRows
     * @param request
     * @param session
     * @return
     * @throws Exception
     */
    @RequestMapping("/saveCacheYF")
    @ResponseBody
    public boolean saveCacheYF(@RequestBody FxReturnObj maps,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            flag = this.faxingService.saveCacheYF(maps.getSelRows(), maps.getSkid());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }


    /**
     *收款核销中的临时表数据（作者预付）   (作者核销单使用)
     * @return
     * @throws Exception
     */
    @RequestMapping("/findYFData")
    @ResponseBody
    public List findYFData(String skId,String clientId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            list= this.faxingService.findYFData(skId, clientId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 查询客户的到款单数据
     * @param dsId
     * @param clientId
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/queryKUYF")
    @ResponseBody
    public QueryJson queryKUYF(String dsId,String clientId,String skid,String skidcache,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            // if ("".equals(skidcache) || skidcache == null) {
            sql = sql+" AND NVL((SELECT SUM(NVL(dk.yf_money_use, 0) + NVL(dk.yf_money_occupy, 0)) DK_TOTAL_MONEY FROM BJ_ZZYFK dk WHERE   DK.yf_id =D.Dj_Code AND dk.yf_id NOT IN ('"+skid+"')) ,0) < d.PREPAID_AMOUNT AND d.INTERPRETER_MAN = '"+clientId+"'";
            //}else if(!skidcache.equals(skid)){
            //	sql = sql+"  AND  NVL((SELECT SUM(NVL(dk.dk_money_use,0)+NVL(dk.dk_money_occupy,0)) FROM FX_SK_DK dk WHERE dk.dk_id=d.dk_id AND dk.sk_id NOT IN ('"+skid+"') ),0)<d.dk_money AND d.CLIENT_ID = '"+clientId+"'";
            // }else{
            // 	sql = sql+"  AND  NVL((SELECT SUM(NVL(dk.dk_money_use,0)+NVL(dk.dk_money_occupy,0)) FROM FX_SK_DK dk WHERE dk.dk_id=d.dk_id AND dk.sk_id NOT IN ('"+skid+"') ),0)<d.dk_money AND d.CLIENT_ID = '"+clientId+"' and sk_id = '"+skid+"'";
            //}
            //执行sql 返回list<Object>
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }


    /**
     * 根据付款对象，查询预付金额之和   （所有的可用金额）
     * @param JIAFANG   付款对象
     * @return
     * @throws Exception
     */
    @RequestMapping("/getzzyfdSum_yfmoney")
    @ResponseBody
    public double getzzyfdSum_yfmoney(String JIAFANG ){
        double  yfmoney=0;
        try {
            yfmoney = this.faxingService.getzzyfdSum_yfmoney(JIAFANG);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return yfmoney;
    }


    @RequestMapping("/saveYF")
    @ResponseBody
    public double saveYF(String skId,String cid,String djcode,@RequestBody List<List<Map<String, String>>> skOptions,HttpServletRequest request,HttpSession session) throws Exception{
        double list = 0;
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            list = this.faxingService.saveYFFunc(skId,cid,djcode,skOptions.get(0), uid.toString()); //保存收款和到款的关系
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /***
     * 使用预付，清除金额的方法    (作者核销单)
     */
    @RequestMapping("/zzhxdremovemoney")
    @ResponseBody
    public String zzhxdremovemoney(String djcode,HttpServletRequest request,HttpSession session) throws Exception{
        String str = "";
        try {
            str = this.faxingService.zzhxdremovemoney(djcode);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return str;
    }



    /***
     * 判断弹框界面的预付单，是否要清楚数据   (作者核销单)
     */
    @RequestMapping("/selremove")
    @ResponseBody
    public int selremove(String djcode,String YF_ID,HttpServletRequest request,HttpSession session) throws Exception{
        int str = 0;
        try {
            str = this.faxingService.selremove(djcode, YF_ID);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return str;
    }

    /**
     *根据选题名称id，计算该选题下所有的印数  (营销预算额度表)
     * @param VENDOR_ID
     * @param request
     * @return
     */
    @RequestMapping("/getYINSHUsum")
    @ResponseBody
    public int getYINSHUsum(String VENDOR_ID,String YINCI,HttpServletRequest request,HttpSession session) throws Exception{
        return faxingService.getYINSHUsum(VENDOR_ID,YINCI);
    }

    /**
     *报销费用的临时表数据（预算）查询报销费用的营销额度表
     * @return
     * @param ds_id
     * @param clientId
     * @throws Exception
     */
    @RequestMapping("/findBXFYEDYs")
    @ResponseBody
    public QueryJson findBXFYEDYs(String dsId,String clientId,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据付款对象查询出该付款对象关联的所有的预付单
            String storeSql = "SELECT * FROM SYS_DATASTORE S WHERE S.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            sql="select * from ("+sql+") temp where temp.XT_ID='"+clientId+"'";
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /**
     * 设置营销预算额度表的默认值
     * @param dsId
     * @param VENDOR_ID
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("/queryBXFYYS")
    @ResponseBody
    public QueryJson queryBXFYYS(String dsId,String clientId,String chid,String skidcache,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            String storeSql = "SELECT * FROM SYS_DATASTORE VV WHERE VV.DS_ID = '"+dsId+"'";
            dataStores = this.faxingService.execSql(storeSql);
            String sql = dataStores.get(0).get("SQL_TEXT").toString();
            if ("".equals(skidcache) || skidcache == null) {
                sql = sql+" AND NVL((SELECT SUM(nvl(sy.BX_MONEY_USE,0)+nvl(sy.BX_MONEY_OCCUPY,0)) FROM CW_BXYXYS sy WHERE sy.YXYS_ID=VV.yxysedb_id AND sy.BXDJ_ID NOT IN ('"+chid+"')),0)<VV.PREPAID_BALANCE AND  VV.XT_ID = '"+clientId+"'";
            }else if(!skidcache.equals(chid)){
                sql = sql+" AND NVL((SELECT SUM(nvl(sy.BX_MONEY_USE,0)+nvl(sy.BX_MONEY_OCCUPY,0)) FROM CW_BXYXYS sy WHERE sy.YXYS_ID=VV.yxysedb_id AND sy.BXDJ_ID NOT IN ('"+chid+"')),0)<VV.PREPAID_BALANCE AND  VV.XT_ID = '"+clientId+"'";
            }else{
                sql = sql+" AND NVL((SELECT SUM(nvl(sy.BX_MONEY_USE,0)+nvl(sy.BX_MONEY_OCCUPY,0)) FROM CW_BXYXYS sy WHERE sy.YXYS_ID=VV.yxysedb_id AND sy.BXDJ_ID NOT IN ('"+chid+"')),0)<VV.PREPAID_BALANCE AND  VV.XT_ID = '"+clientId+"' and BXDJ_ID = '"+chid+"'";
            }
            //执行sql 返回list<Object>
            list = this.faxingService.execSql(sql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /***
     * 费用报销，清除金额的方法
     */
    @RequestMapping("/bxfyedremovemoney")
    @ResponseBody
    public String bxfyedremovemoney(String chid,HttpServletRequest request,HttpSession session) throws Exception{
        String str = "";
        try {
            str = this.faxingService.bxfyremovemoney(chid);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return str;
    }


    /**
     * 保存报销费用和第三张表的关联表
     * @param jsId
     * @param ysAllOption
     * @param request
     * @param session
     * @return
     * modify：
     * @throws Exception
     */
    @RequestMapping("/saveBxfyYS")
    @ResponseBody
    public boolean saveBxfyYS(String skId,String YSID,String XT_ID,@RequestBody List<List<Map<String, String>>> skOptions,HttpServletRequest request,HttpSession session) throws Exception{
        boolean flag = false;
        try {
            Object uid = session.getAttribute(SessionCont.uid);
            if (uid == null || "".equals(uid.toString())) {
                System.out.println("用户未登录 SingleController L.138");
            }
            flag = this.faxingService.saveBxfyYSFunc(skId,YSID, XT_ID, skOptions.get(0), uid.toString()); //保存收款和预收的关系
        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }

    /**
     *根据选题名称id，，获得定价   (营销预算额度表)
     * @param VENDOR_ID
     * @param request
     * @return
     */
    @RequestMapping("/getcontractnobyxtid")
    @ResponseBody
    public List getcontractnobyxtid(String VENDOR_ID,HttpServletRequest request,HttpSession session){
        return faxingService.getcontractnobyxtid(VENDOR_ID);
    }



    /**
     *  (客户回款计划)
     * @param VENDOR_ID    片区经理
     * @param request
     * @return
     */
    @RequestMapping("/getcontractnobykhhkjh")
    @ResponseBody
    public List getcontractnobykhhkjh(String VENDOR_ID,String KH_DATE,HttpServletRequest request,HttpSession session) throws Exception{
        return faxingService.getcontractnobykhhkjh(VENDOR_ID,KH_DATE);
    }



    /**
     * 客户实销上报(按日)
     * 判断客户输入的当前月份日期是否已经上报过数据
     */
    @RequestMapping("/getFxCIdByDate")
    @ResponseBody
    public QueryJson getFxCIdByDate(String clientId,String Date,String CSID,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据客户ID判断该客户是否在该月上报过数据
            String storeSql = "select * from fx_clientsales cm where cm.client_id = '"+clientId+"' and cm.is_destroy = '0' and to_char(cm.import_date) = to_char(to_date('"+Date+"', 'yy-mm-dd hh24:mi:ss'))and cm.cs_id not in (select c.cs_id from fx_clientsales c where c.cs_id='"+CSID+"')";
            list = this.faxingService.execSql(storeSql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /**
     * 客户实销上报(按月)
     * 判断客户输入的当前月份日期是否已经上报过数据
     */
    @RequestMapping("/getFxCCIdByDate")
    @ResponseBody
    public QueryJson getFxCCIdByDate(String clientId,String Date,String CMID,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据客户ID判断该客户是否在该月上报过数据
            String storeSql = "SELECT * FROM Fx_Monthsx cm WHERE cm.client_id = '"+clientId+"' AND cm.is_destroy = '0' AND to_char(cm.import_date, 'YYYY') = to_char(to_date('"+Date+"', 'yy-mm-dd hh24:mi:ss'), 'YYYY') AND to_char(cm.import_date, 'MM') = to_char(to_date('"+Date+"', 'yy-mm-dd hh24:mi:ss'), 'MM') AND cm.cm_id NOT IN (SELECT c.cm_id FROM Fx_Monthsx c WHERE c.cm_id = '"+CMID+"')";
            list = this.faxingService.execSql(storeSql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /**
     * 客户在途上报（按月）
     * 判断客户输入的当前月份日期是否已经上报过数据
     */
    @RequestMapping("/getFxZTByDate")
    @ResponseBody
    public QueryJson getFxZTByDate(String clientId,String Date,String ztId,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据客户ID判断该客户是否在该月上报过数据
            String storeSql = "SELECT * FROM Fx_MonthZT cm WHERE cm.client_id = '"+clientId+"' AND cm.is_destroy = '0' AND to_char(cm.import_date, 'yyyy-MM') = to_char(to_date('"+Date+"', 'yy-mm-dd hh24:mi:ss'), 'yyyy-MM') AND cm.zt_id NOT IN (SELECT c.zt_id FROM Fx_MonthZT c WHERE c.zt_id = '"+ztId+"')";
            list = this.faxingService.execSql(storeSql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /**
     * 客户差异消耗上报（按月）
     * 判断客户输入的当前月份日期是否已经上报过数据
     */
    @RequestMapping("/getFxCYXHByDate")
    @ResponseBody
    public QueryJson getFxCYXHByDate(String clientId,String Date,String cyId,HttpServletRequest request) throws Exception{
        QueryJson queryJson = new QueryJson();
        List list = new ArrayList();
        List<Map<String,Object>> dataStores = new ArrayList<Map<String,Object>>();
        try {
            //根据客户ID判断该客户是否在该月上报过数据
            String storeSql = "SELECT * FROM Fx_MonthCYXH cm WHERE cm.client_id = '"+clientId+"' AND cm.is_destroy = '0' AND to_char(cm.import_date, 'yyyy-MM') = to_char(to_date('"+Date+"', 'yy-mm-dd hh24:mi:ss'), 'yyyy-MM') AND cm.CY_id NOT IN (SELECT c.CY_id FROM Fx_MonthCYXH c WHERE c.CY_id = '"+cyId+"')";
            list = this.faxingService.execSql(storeSql);
            queryJson.setRows(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryJson;
    }

    /***
     * 加载客户实销上报统计（按日）的对照信息
     * ,String date2,String date3
     * , date2, date3
     */
    @RequestMapping("/loadclientbyData")
    @ResponseBody
    public QueryJson loadclientbyData(DataGridReceiveParams dataGParams,String dsId,String date1,HttpServletRequest request,HttpSession session) throws Exception{
        QueryJson queryJson=this.faxingService.loadclientbyData(dsId, date1);
        return queryJson;
    }


    /***
     * 客户实销上报统计(按日)
     */
    @RequestMapping("/jxlfords_client_zq_dgparent1")
    @ResponseBody
    public QueryJson jxlfords_client_zq_dgparent1(DataGridReceiveParams dataGParams,String dsId,String date1,HttpServletRequest request,HttpSession session,HttpServletResponse response) throws Exception{
        QueryJson queryJson=this.faxingService.loadclientbyData(dsId, date1);
        int in=0;

//        Map<String, String>map=faxingService.getname(in);
        List list2=queryJson.getRows();
        String rmark ="导出成功";


        try{
            String fileName="客户实销上报统计(按日)";
            response.setContentType( "application/vnd.ms-excel");
            response.addHeader( "Content-Disposition","attachment; filename=\"" + new String(fileName.getBytes("GBK"),"iso-8859-1")+".xls" + "\"" );
            OutputStream os = response.getOutputStream();
            WritableWorkbook wwb = Workbook.createWorkbook(os);

            WritableFont wtf = new WritableFont(WritableFont.createFont("宋体"),14,WritableFont.BOLD,false);
            WritableCellFormat wcfmt = new WritableCellFormat(wtf);
            wcfmt.setAlignment(jxl.format.Alignment.CENTRE);//居中

            WritableFont wtf2 = new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
            WritableCellFormat wcfmt2 = new WritableCellFormat(wtf2);
            wcfmt2.setAlignment(jxl.format.Alignment.CENTRE);//居中

            WritableFont wtf6 = new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.BOLD,false);
            WritableCellFormat wcfmt6 = new WritableCellFormat(wtf6);
            wcfmt6.setAlignment(jxl.format.Alignment.CENTRE);//居中

            WritableFont wtf3 = new WritableFont(WritableFont.createFont("宋体"),14,WritableFont.BOLD,false);
            WritableCellFormat wcfmt3 = new WritableCellFormat(wtf3);
            wcfmt3.setAlignment(jxl.format.Alignment.CENTRE);//居中
            wcfmt3.setBackground(Colour.GRAY_25);//"#C6DBFA"

            WritableFont wtf4 = new WritableFont(WritableFont.createFont("宋体"),14,WritableFont.BOLD,false);
            WritableCellFormat wcfmt4 = new WritableCellFormat(wtf4);
            wcfmt4.setAlignment(jxl.format.Alignment.CENTRE);//居中
            wcfmt4.setBackground(Colour.BLUE);//"#C6DBFA"

            WritableFont wtf5 = new WritableFont(WritableFont.createFont("宋体"),14,WritableFont.BOLD,false);
            WritableCellFormat wcfmt5 = new WritableCellFormat(wtf5);
            wcfmt5.setAlignment(jxl.format.Alignment.CENTRE);//居中
            wcfmt5.setBackground(Colour.CORAL);//"#C6DBFA"
            //脚下爱你特

            int ncout =list2.size();
            int maxnum = 50000; //一次最多写入量
            int times = (ncout+maxnum-1)/maxnum;
            //大循环
                for(int t=0; t<times; t++){

                    //新建一张表
                    WritableSheet wsheet = wwb.createSheet( "members_"+(t+1),t);
                    //设置表头
                    wsheet.setColumnView(0, 15); // 设置列的宽度
                    wsheet.setColumnView(1, 15); // 设置列的宽度
                    wsheet.setColumnView(2, 25); // 设置列的宽度
                    wsheet.setColumnView(3, 25); // 设置列的宽度
                    wsheet.setColumnView(4, 25); // 设置列的宽度
                    wsheet.setColumnView(5, 25); // 设置列的宽度
                    wsheet.setColumnView(6, 25); // 设置列的宽度
                    wsheet.setColumnView(7, 25); // 设置列的宽度
                    wsheet.setColumnView(8, 25); // 设置列的宽度
                    wsheet.setColumnView(9, 25); // 设置列的宽度
                    wsheet.setColumnView(10, 25); // 设置列的宽度
                    wsheet.setColumnView(11, 25); // 设置列的宽度
                    wsheet.setColumnView(12, 25); // 设置列的宽度
                    wsheet.setColumnView(13, 25); // 设置列的宽度
                    wsheet.setColumnView(14, 25); // 设置列的宽度
                    wsheet.setColumnView(15, 25); // 设置列的宽度
                    wsheet.setColumnView(16, 25); // 设置列的宽度
                    wsheet.setColumnView(17, 25); // 设置列的宽度
                    wsheet.setColumnView(18, 25); // 设置列的宽度
                    wsheet.setColumnView(19, 25); // 设置列的宽度
                    wsheet.setColumnView(20, 25); // 设置列的宽度
                    wsheet.setColumnView(21, 25); // 设置列的宽度
                    wsheet.setColumnView(22, 25); // 设置列的宽度
                    wsheet.setColumnView(23, 25); // 设置列的宽度
                    wsheet.setColumnView(24, 25); // 设置列的宽度
                    wsheet.setColumnView(25, 25); // 设置列的宽度
                    wsheet.setColumnView(26, 25); // 设置列的宽度
                    wsheet.setColumnView(27, 25); // 设置列的宽度
                    wsheet.setColumnView(28, 25); // 设置列的宽度
                    wsheet.setColumnView(29, 25); // 设置列的宽度
                    wsheet.setColumnView(30, 25); // 设置列的宽度
                    wsheet.setColumnView(31, 25); // 设置列的宽度
                    wsheet.setColumnView(32, 25); // 设置列的宽度

                    Label label = new Label(0,0,"客户实销上报统计(按日)-------"+date1 ,wcfmt);
                    wsheet.addCell(label);
                    wsheet.mergeCells(0, 0, 5, 0);
//                    label = new Label(1,1,date1,wcfmt3 );
//                    wsheet.addCell(label);
//                    wsheet.mergeCells(1, 1, 3, 1);


                    label = new Label(0,2,"商品名称" ,wcfmt);
                    wsheet.addCell(label);
                    label = new Label(1,2,"单价" ,wcfmt);
                    wsheet.addCell(label);
                    label = new Label(2,2,date1+"-1日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(3,2,date1+"-2日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(4,2,date1+"-3日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(5,2,date1+"-4日实销数",wcfmt );
                    wsheet.addCell(label);

                    label = new Label(6,2,date1+"-5日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(7,2,date1+"-6日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(8,2,date1+"-7日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(9,2,date1+"-8日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(10,2,date1+"-9日实销数",wcfmt );
                    wsheet.addCell(label);

                    label = new Label(11,2,date1+"-10日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(12,2,date1+"-11日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(13,2,date1+"-12日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(14,2,date1+"-13日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(15,2,date1+"-14日实销数",wcfmt );
                    wsheet.addCell(label);

                    label = new Label(16,2,date1+"-15日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(17,2,date1+"-16日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(18,2,date1+"-17日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(19,2,date1+"-18日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(20,2,date1+"-19日实销数",wcfmt );
                    wsheet.addCell(label);

                    label = new Label(21,2,date1+"-20日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(22,2,date1+"-21日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(23,2,date1+"-22日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(24,2,date1+"-23日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(25,2,date1+"-24日实销数",wcfmt );
                    wsheet.addCell(label);

                    label = new Label(26,2,date1+"-25日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(27,2,date1+"-26日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(28,2,date1+"-27日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(29,2,date1+"-28日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(30,2,date1+"-29日实销数",wcfmt );
                    wsheet.addCell(label);

                    label = new Label(31,2,date1+"-30日实销数",wcfmt );
                    wsheet.addCell(label);
                    label = new Label(32,2,date1+"-31日实销数",wcfmt );
                    wsheet.addCell(label);

                    int base = (t*maxnum);
                    int overbase=(t+1)*maxnum;
                    if (overbase>list2.size()) {
                        overbase=list2.size()-base;
                    }else {
                        overbase=maxnum;
                    }

                    if (list2.size() > 0) {
                        for (int i =0; i < overbase; i++) {
                            int index=t*maxnum+i;
                            JSONObject jsonObject = JSONObject.fromObject(list2.get(index));

                            label = new Label(0,(i+3),jsonObject.getString("H_ID").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(1,(i+3),jsonObject.getString("H_PRICE").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(2,(i+3),jsonObject.getString("ONE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(3,(i+3),jsonObject.getString("TWO_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(4,(i+3),jsonObject.getString("THREE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);

                            label = new Label(5,(i+3),jsonObject.getString("FOUR_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(6,(i+3),jsonObject.getString("FIVE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(7,(i+3),jsonObject.getString("SIX_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(8,(i+3),jsonObject.getString("SEVEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(9,(i+3),jsonObject.getString("EIGHT_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);

                            label = new Label(10,(i+3),jsonObject.getString("NINE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(11,(i+3),jsonObject.getString("TEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(12,(i+3),jsonObject.getString("ELEVEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(13,(i+3),jsonObject.getString("TWELVE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(14,(i+3),jsonObject.getString("THIRTEEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);

                            label = new Label(15,(i+3),jsonObject.getString("FOURTEEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(16,(i+3),jsonObject.getString("FIFTEEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(17,(i+3),jsonObject.getString("SIXTEEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(18,(i+3),jsonObject.getString("SEVENTEEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(19,(i+3),jsonObject.getString("EIGHTEEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);

                            label = new Label(20,(i+3),jsonObject.getString("NINETEEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(21,(i+3),jsonObject.getString("TWENTY_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(22,(i+3),jsonObject.getString("TWENTY_ONE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(23,(i+3),jsonObject.getString("TWENTY_TWO_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(24,(i+3),jsonObject.getString("TWENTY_THREE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);

                            label = new Label(25,(i+3),jsonObject.getString("TWENTY_FOUR_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(26,(i+3),jsonObject.getString("TWENTY_FIVE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(27,(i+3),jsonObject.getString("TWENTY_SIX_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(28,(i+3),jsonObject.getString("TWENTY_SEVEN_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(29,(i+3),jsonObject.getString("TWENTY_EIGHT_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);

                            label = new Label(30,(i+3),jsonObject.getString("TWENTY_NINE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(31,(i+3),jsonObject.getString("THIRTY_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                            label = new Label(32,(i+3),jsonObject.getString("THIRTY_ONE_AMOUNT").replaceAll("null", ""),wcfmt6);
                            wsheet.addCell(label);
                        }
                        label = new Label(0, 9 + list2.size() + 5  + 12, "技术支持:上海爱你特科技www.ainit.cn");
                        wsheet.addCell(label);
                        wsheet.mergeCells(0, 9 + list2.size() + 5 + 12, 9, 9 + list2.size() + 5  + 12);
                    }

                }

            wwb.write();
            wwb.close();
            os.flush();
        }
        catch (Exception e) {
            System.out.println(e);
            System. out.println("生成信息表(Excel格式)时出错：" );
            rmark="导出失败";
            e.printStackTrace();
        }
        return queryJson;

    }


    /**
     * 根据报销费用ID查询关联表中的报销费用是否重复
     *
     */
    @RequestMapping("/findbxData")
    @ResponseBody
    public List findbxData(String bxId,HttpServletRequest request) throws Exception{
        List list = new ArrayList();
        try {
            if(!"".equals(bxId)){
                list = this.faxingService.getfindbxData(bxId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}





